﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Collections;
using System.Data.Common;

namespace Transcend.DP3.Db
{

	/// <summary>
	/// 数据库帮助类，该类实例不是线程安全的
	/// </summary>
	public class Database : AbstractDatabase, IDisposable
	{

		/// <summary>
		/// 读取大对象默认缓冲区大小
		/// </summary>
		public const int LOB_BUFFER_SIZE = 4096;

		private string connString;
		/// <summary>
		/// 数据库连接字符串
		/// </summary>m
		public string ConnectionString
		{
			get { return connString; }
		}

		[ThreadStaticAttribute]
		private DbConnection conn;
		/// <summary>
		/// 数据库连接实例
		/// </summary>
		public DbConnection Connection
		{
			get { return conn; }
		}

		[ThreadStaticAttribute]
		private DbTransaction trans;

		public Database(string connString)
		{
			this.connString = connString;
			conn = mkConnection(connString);
		}

		/// <summary>
		/// 如果使用了using语句块,该方式使用完成将自动销毁数据库连接
		/// </summary>
		public Database(DbConnection conn)
		{
			if (conn.State != System.Data.ConnectionState.Open) conn.Open();
			this.conn = conn;
		}

		/// <summary>
		/// 创建命令对象
		/// </summary>
		/// <param name="cmdText"></param>
		/// <param name="cmdType"></param>
		/// <returns></returns>
		public DbCommand mkCommand(string cmdText, System.Data.CommandType cmdType)
		{
			var cmd = conn.CreateCommand();
			cmd.CommandText = cmdText;
			cmd.CommandType = cmdType;
			return cmd;
		}

		/// <summary>
		/// 按照顺序绑定参数
		/// </summary>
		/// <param name="cmdText"></param>
		/// <param name="param"></param>
		/// <returns></returns>
		public DbCommand mkCommand(string cmdText, List<DbParameter> param)
		{
			var cmd = mkCommand(cmdText, System.Data.CommandType.Text);
			foreach (DbParameter p in param)
			{
				cmd.Parameters.Add(p);
			}
			return cmd;
		}

		public DbCommand mkCommand(string cmdText, params object[] param)
		{
			var cmd = mkCommand(cmdText, System.Data.CommandType.Text);
			int i = 0;
			foreach (object p in param)
			{
				++i;
				mkParam(cmd, string.Format("p{0}", i), p);
			}
			return cmd;
		}

		#region -------------------------> 执行语句

		public int Execute(string cmdText, List<DbParameter> param)
		{
			using (var cmd = mkCommand(cmdText, param))
			{
				try
				{
					return cmd.ExecuteNonQuery();
				}
				catch (Exception ex)
				{
					throw new DbOperationException(ex, "数据库操作错误：{0}\r\n错误语句:{1}", ex.Message, DumpCommand(cmd));
				}
			}
		}

		public int Execute(string cmdText, params object[] param)
		{
			using (var cmd = mkCommand(cmdText, param))
			{
				try
				{
					return cmd.ExecuteNonQuery();
				}
				catch (Exception ex)
				{
					throw new DbOperationException(ex, "数据库操作错误：{0}\r\n错误语句:{1}", ex.Message, DumpCommand(cmd));
				}
			}
		}

		/// <summary>
		/// 按照参数名称绑定的语句
		/// 参数名称前缀含义： io_:输入/输出参数;ot:_返回参数值;in_:输入参数(默认）
		/// </summary>
		public Dictionary<string, object> Execute(string sql, Dictionary<string, object> paramContext = null)
		{
			// 需要返回的参数值
			Dictionary<string, object> retVals = new Dictionary<string, object>();
			// 需要返回的参数列表
			List<DbParameter> retParams = new List<DbParameter>();
			// 取语句中的参数占位符
			List<string> pNames = ParseParamPlaceholder(sql);

			using (var cmd = conn.CreateCommand())
			{
				cmd.CommandText = sql;
				pNames.ForEach(delegate(string placeHolder)
				{
					DbParameter p = cmd.CreateParameter();
					p.ParameterName = placeHolder;

					string dir = "in_";
					if (placeHolder.Length > 3) dir = placeHolder.Substring(0, 3).ToLower();
					switch (dir)
					{
						case "ot_":
							p.Direction = System.Data.ParameterDirection.Output;
							p.Value = GetParamVal(paramContext, placeHolder);
							retParams.Add(p);
							break;
						case "io_":
							p.Value = GetParamVal(paramContext, placeHolder);
							p.Direction = System.Data.ParameterDirection.InputOutput;
							retParams.Add(p);
							break;
						case "in_":
						default:
							p.Value = GetParamVal(paramContext, placeHolder);
							p.Direction = System.Data.ParameterDirection.Input;
							break;
					}
					cmd.Parameters.Add(p);
				});

				try
				{
					cmd.ExecuteNonQuery();
				}
				catch (Exception ex)
				{
					throw new DbOperationException(ex, "数据库操作错误:{0}，错误语句：{1}", ex.Message, DumpCommand(cmd), ex);
				}

				retParams.ForEach(delegate(DbParameter p)
				{
					if (retVals.ContainsKey(p.ParameterName))
					{
						retVals[p.ParameterName] = NormalizeDbVal(p.Value);
					}
					else
					{
						retVals.Add(p.ParameterName, NormalizeDbVal(p.Value));
					}
				});
			}

			return retVals;
		}

		public void ExecuteProc(string cmdText, params DbParameter[] param)
		{
			using (var cmd = mkCommand(cmdText))
			{
				cmd.CommandType = System.Data.CommandType.StoredProcedure;
				foreach (DbParameter p in param)
				{
					cmd.Parameters.Add(p);
				}
				try
				{
					cmd.ExecuteNonQuery();
				}
				catch (Exception ex)
				{
					throw new DbOperationException(ex, "数据库操作错误：{0}\r\n错误语句:{1}", ex.Message, DumpCommand(cmd));
				}
			}
		}

		#endregion

		#region -------------------------> 查询

		/// <summary>
		/// 给定查询在是否存在至少一行数据
		/// </summary>
		public bool HasRow(string cmdText, params object[] param)
		{
			using (var cmd = mkCommand(cmdText, param))
			{
				try
				{
					using (var dr = cmd.ExecuteReader())
					{
						return dr.Read();
					}
				}
				catch (Exception ex)
				{
					throw new DbOperationException(ex, "数据库操作错误：{0}\r\n错误语句:{1}", ex.Message, DumpCommand(cmd));
				}
			}
		}

		/// <summary>
		/// 返回首行的第一个字段的值
		/// 注：仅限基本数据类型
		/// </summary>
		public void QueryField<T>(ref T val, string cmdText, params object[] param)
		{

			object dbVal = null;
			using (var cmd = mkCommand(cmdText, param))
			{
				try
				{
					using (var dr = cmd.ExecuteReader())
					{
						if (dr.Read())
						{
							dbVal = NormalizeDbVal(dr[0]);
						}
					}
				}
				catch (Exception ex)
				{
					throw new DbOperationException(ex, "数据库操作错误：{0}\r\n错误语句:{1}", ex.Message, DumpCommand(cmd));
				}
			}

			if (dbVal != null) val = (T)Convert.ChangeType(dbVal, typeof(T));
		}

		/// <summary>
		/// 取reader中每一行的第一个字段放入list
		/// </summary>
		public List<T> QueryField<T>(string cmdText, params object[] param)
		{
			var ret = new List<T>();
			using (var cmd = mkCommand(cmdText, param))
			{
				try
				{
					using (var dr = cmd.ExecuteReader())
					{
						while (dr.Read())
						{
							ret.Add(GetValue<T>(dr, 0));
						}
					}
				}
				catch (Exception ex)
				{
					throw new DbOperationException(ex, "数据库操作错误：{0}\r\n错误语句:{1}", ex.Message, DumpCommand(cmd));
				}
			}
			return ret;
		}

		/// <summary>
		/// 以数组的形式返回第一行数据
		/// </summary>
		public object[] QueryRowAsArray(string cmdText, params object[] param)
		{

			object[] array = null;
			using (var cmd = mkCommand(cmdText, param))
			{
				try
				{
					using (DbDataReader dr = cmd.ExecuteReader())
					{
						if (dr.Read())
						{
							array = new object[dr.FieldCount];
							for (int i = 0; i < array.Length; i++)
							{
								array[i] = dr[i];
							}
						}
					}
				}
				catch (Exception ex)
				{
					throw new DbOperationException(ex, "数据库操作错误：{0}\r\n错误语句:{1}", ex.Message, DumpCommand(cmd));
				}
			}
			return array;
		}

		/// <summary>
		/// 读取第一行到dictionary,列名称不能重复
		/// </summary>
		public Dictionary<string, object> QueryRowAsDictionary(string cmdText, params object[] param)
		{
			using (var cmd = mkCommand(cmdText, param))
			{
				try
				{
					using (DbDataReader dr = cmd.ExecuteReader())
					{
						if (dr.Read())
						{
							return RowToDic(dr);
						}
					}
				}
				catch (Exception ex)
				{
					throw new DbOperationException(ex, "数据库操作错误：{0}\r\n错误语句:{1}", ex.Message, DumpCommand(cmd));
				}
			}

			return new Dictionary<string, object>(0);
		}

		/// <summary>
		/// 返回类型实例
		/// </summary>
		public T QueryObject<T>(IReaderMapper<T> mapper, string cmdText, params object[] param)
		{
			T val = default(T);
			using (var cmd = mkCommand(cmdText, param))
			{
				try
				{
					using (var dr = cmd.ExecuteReader())
					{
						if (dr.Read())
						{
							val = mapper.Map(dr);
						}
					}
				}
				catch (Exception ex)
				{
					throw new DbOperationException(ex, "数据库操作错误：{0}\r\n错误语句:{1}", ex.Message, DumpCommand(cmd));
				}
			}
			return val;
		}

		/// <summary>
		/// 返回对象结合
		/// </summary>
		public List<T> QueryList<T>(IReaderMapper<T> mapper, string cmdText, params object[] param)
		{
			List<T> list = new List<T>(1);
			using (var cmd = mkCommand(cmdText, param))
			{
				try
				{
					using (var dr = cmd.ExecuteReader())
					{
						while (dr.Read())
						{
							list.Add(mapper.Map(dr));
						}
					}
				}
				catch (Exception ex)
				{
					throw new DbOperationException(ex, "数据库操作错误：{0}\r\n错误语句:{1}", ex.Message, DumpCommand(cmd));
				}
			}
			return list;
		}

		public T QueryObject<T>(string cmdText, params object[] param)
		{
			T ret = default(T);
			using (var cmd = mkCommand(cmdText, param))
			{
				try
				{
					using (var dr = cmd.ExecuteReader())
					{
						ret = MapRow<T>(dr);
					}
				}
				catch (Exception ex)
				{
					throw new DbOperationException(ex, "数据库操作错误：{0}\r\n错误语句:{1}", ex.Message, DumpCommand(cmd));
				}
			}
			return ret;
		}

		public List<T> QueryList<T>(string cmdText, params object[] param)
		{
			throw new NotImplementedException("方法没有实现");
		}

		public System.Data.DataTable QueryDataTable(string cmdText, params object[] param)
		{

			var dataTable = new System.Data.DataTable();

			using (var cmd = mkCommand(cmdText, param))
			{
				try
				{
					using (var dr = cmd.ExecuteReader())
					{
						dataTable.Load(dr, System.Data.LoadOption.PreserveChanges);
					}
				}
				catch (Exception ex)
				{
					throw new DbOperationException(ex, "数据操作出错：{0}\r\n错误语句:{1}", ex.Message, DumpCommand(cmd));
				}
			}

			return dataTable;
		}

		#endregion

		#region -------------------------> 事务

		/// <summary>
		/// 启动事务
		/// </summary>
		public void BeginTrans()
		{
			if (trans != null)
			{
				throw new DbOperationException("已打开的事务没有回滚/提交");
			}
			trans = conn.BeginTransaction();
		}

		/// <summary>
		/// 回滚事物
		/// </summary>
		public void Rollback()
		{
			if (trans == null) return;
			trans.Rollback();
			trans = null;
		}

		/// <summary>
		/// 提交事物
		/// </summary>
		public void Commit()
		{
			if (trans == null) return;
			trans.Commit();
			trans = null;
		}

		#endregion

		#region -------------------------> 辅助函数

		/// <summary>
		/// 
		/// </summary>
		public static void OraLongFetchSize(DbCommand cmd, int size = -1)
		{
			var t = cmd.GetType();
			var p = t.GetProperty("InitialLONGFetchSize");
			if (p != null)
			{
				p.SetValue(cmd, size, null);
			}
		}

		/// <summary>
		/// 
		/// </summary>
		public static void OraLobFetchSize(DbCommand cmd, int size = -1)
		{
			var t = cmd.GetType();
			var p = t.GetProperty("InitialLOBFetchSize");
			if (p != null)
			{
				p.SetValue(cmd, size, null);
			}
		}

		/// <summary>
		/// 返回reader中除去排除字段外的字段名称
		/// </summary>
		/// <param name="dr">reader</param>
		/// <param name="excFields">排除字段</param>
		/// <returns></returns>
		public static string [] GetFields(DbDataReader dr, params string[] excFields)
		{
			var allFields = new System.Collections.Specialized.StringCollection();
			var fieldName = string.Empty;
			for (var i = 0; i < dr.FieldCount; i++)
			{
				fieldName = dr.GetName(i);
				if (excFields != null)
				{
					foreach (var exf in excFields)
					{
						// 如果包含在排除字段内
						if (fieldName.Equals(exf, StringComparison.OrdinalIgnoreCase))
						{
							continue;
						}
					}
				}
				allFields.Add(fieldName);
			}
			var fields = new string[allFields.Count];
			allFields.CopyTo(fields, 0);
			return fields;
		}

		#endregion

		#region -------------------------> 资源释放

		/// <summary>
		/// 作用相当于调用Dispose
		/// </summary>
		public void Release()
		{
			if (trans != null)
			{
				trans.Commit();
			}

			if (conn != null)
			{
				conn.Close();
			}
		}

		public void Dispose()
		{
			Release();
		}

		#endregion

	}

}
